/**   
 * Copyright (c) 2015-2020 ZJW. All Rights Reserved.   
 * =================================================  
 * Nothing can replace hard work.
 * Talk is cheap,show me the code.
 * =================================================
 *
 */  
package com.zjw.jdbc.util;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

/**
 * @Description: batch insert utils
 * @author: ZhengJunWu
 * @date: 2015年1月23日 下午5:52:15
 *
 */
public class BatchAddUtil {
	private static final int VALUES_COUNT = 50;
	private static final int BATCH_SIZE = 100;


	/**
	 * 1.setAutoCommit(false) 
	 * 2.insert into table(tableField...) values(?,?...),(?,?...),(?,?...)...
	 * 3.addBatch(),executeBatch()
	 * @param conn
	 * @param tableName
	 * @param tableFields
	 * @param params
	 * @param insertCount
	 */
	public static void batchInsert(Connection conn, String tableName,
			String tableFields, Object[] params, int insertCount) {
		PreparedStatement ps = null;
		int fieldsCount = tableFields.split(",").length;
		int sqlCount = insertCount / VALUES_COUNT;
		String sql = makeSql(tableName, tableFields, VALUES_COUNT);
		System.out.println("sql : " + sql);
		long startTime = System.currentTimeMillis();
		
		try {
			conn = JdbcUtil.getConnection();
			conn.setAutoCommit(false);
			ps = conn.prepareStatement(sql);
			for (int i = 0; i < sqlCount; i++) {
				for (int j = 0; j < VALUES_COUNT * fieldsCount;j++ ) {
					Object obj = params[j % fieldsCount];
					ps.setObject(j+1,obj);
				}
				ps.addBatch();
				if((i+1) % BATCH_SIZE == 0){
					ps.executeBatch();
				}
			}
			if(sqlCount % BATCH_SIZE != 0){
				ps.executeBatch();
			}
			
			System.out.println("totalTime: " + (System.currentTimeMillis() - startTime));
			conn.commit();
		} catch (SQLException e) {
			e.printStackTrace();
			JdbcUtil.rollback(conn);
		} finally{
			JdbcUtil.closeStatement(ps);
		}
	}
	
	/**
	 * generate batch insert sql
	 * @param tableName table's name
	 * @param tableFields field1,field2,field3
	 * @param valuesCount how many (?,?,?)
	 * @return INSERT INTO table (field1,field2,field3) VALUES(?,?,?),(?,?,?),(?,?,?)
	 */
	private static String makeSql(String tableName,String tableFields,int valuesCount){
		StringBuilder sqlBuilder = new StringBuilder(1024);
		sqlBuilder.append("INSERT INTO ")
				  .append(tableName)
				  .append("(" + tableFields + ") VALUES ");
		int fieldCount = tableFields.split(",").length;
		String questionStr = makeQuestionMarkStr(fieldCount);
		for(int i=0;i<valuesCount;i++){
			sqlBuilder.append(questionStr + ",");
		}
		sqlBuilder.deleteCharAt(sqlBuilder.length()-1);
		return sqlBuilder.toString();
	}
	
	/**
	 * 
	 * @param count
	 * @return count == 3 ? return (?,?,?)
	 */
	private static String makeQuestionMarkStr(int count){
		if(count < 1) return null;
		StringBuilder sb = new StringBuilder();
		sb.append("(");
		for(int i=0;i<count;i++){
			sb.append("?,");
		}
		sb = sb.deleteCharAt(sb.length()-1);//delete the last ","
		sb.append(")");
		return sb.toString();
	}

}
